-- 特殊件明细 和 特殊件提醒明细 的业务类型code不同, 关联时需要转一下
-- 特殊件, 主键 waybill_no + business_type + 品牌
with detail as (
    select 
          a.waybill_no
        , b.scan_time  as input_time
        ,case a.business_type
                when 'intercept'     then 1
                when 'cancel'        then 2
                when 'usertag'       then 3
                when 'rural'         then 4
                when 'fresh'         then 5
                when 'reback'        then 6
                when 'recievedpay'   then 7
                when 'region'        then 8
                when 'demand'        then 9
                when 'label'         then 11
                when 'special'       then 10
                when 'cod'           then 12
                when 'receipt'       then 13
                when 'city'          then 14
                when 'province'      then 15
                when 'compensated'   then 16
                when 'shsm'          then 17
                when 'tfyz'          then 18
        end                                    as business_type_code 
        ,case a.business_type
                when 'intercept'     then '拦截件'
                when 'cancel'        then '取消件'
                when 'usertag'       then '用户画像'
                when 'rural'         then '乡村件'
                when 'fresh'         then '生鲜件'
                when 'reback'        then '退回件'
                when 'recievedpay'   then '到付款件'
                when 'region'        then '区域拦截件'
                when 'demand'        then '按需配送'
                when 'label'         then '打标客户'
                when 'special'       then '特殊件'
                when 'cod'           then '代收件'
                when 'receipt'       then '回单件'
                when 'city'          then '同城件'
                when 'province'      then '同省件'
                when 'compensated'   then '理赔件'
                when 'shsm'          then '按需配送-送货上门'
                when 'tfyz'          then '按需配送-投放驿站'
        end                                    as business_type_name
        , a.business_type                      as business_type_name_en
        , a.input_network_id
        , nvl(a.input_network_code, b.scan_site_code)  as input_network_code
        , nvl(a.input_network_name, b.scan_site)       as input_network_name
        , b.real_oper_source_code                      as business_id
        , c.business_name                              as business_name
        , is_delete
        , updated_time
        , dt
    from 
        jms_dwd.dwd_tab_ops_intervene_input_dt a
    inner join(
        select 
            waybill_no
            ,real_oper_source_code
            ,scan_site_code
            ,scan_site
            ,scan_time
            ,row_number() over(partition by waybill_no, real_oper_source_code order by scan_time) as rn
        from 
            jms_dwd.dwd_tab_barscan_collect_base_dt
        where 
            dt >= date_add('{{ execution_date  | cst_ds }}', -2) 
            and dt <= '{{ execution_date  | cst_ds }}'
            and network_type = '4'
    ) b
        on a.waybill_no = b.waybill_no and b.rn = 1
    left join 
        jms_dim.dim_tab_terminal_secret_base c
        on b.real_oper_source_code = c.business_id
    where 
        a.dt between date_add('{{ execution_date  | cst_ds }}', -3) and '{{ execution_date  | cst_ds }}'
        and a.is_delete = 0
        and c.app_id = '1010002'  -- 只算驿站数据
)
,remind_detail as (
 select 
        billcode
        , case spec_type
            when '01' then 0   -- 正常件
            when '02' then 5   -- 生鲜件
            when '03' then 1   -- 拦截件
            when '04' then 7   -- 到付件
            when '05' then 19  -- 电联件
            when '06' then 9   -- 按需配送
            when '07' then 12  -- 代收件
            when '08' then 13  -- 回单件
            when '09' then 17  -- 送货上门
        end                       as spec_type
        -- 入库和提醒表用的品牌ID不一致，需要手动映射
        , case business_id
            when '45' then '94'  -- 菜鸟驿站
            when '2'  then '97'  -- 邻里服务点
            else business_id
        end                       as business_id
        , business_name           as business_name
        , count(*)                as remind_cnt
    from 
        jms_dwd.dwd_tab_spec_piece_record_dt
    where 
        dt between date_add('{{ execution_date  | cst_ds }}', -2) and '{{ execution_date  | cst_ds }}' 
        and substr(create_time,1,10) between date_add('{{ execution_date  | cst_ds }}', -2) and '{{ execution_date  | cst_ds }}' 
    group by 
        billcode
        ,spec_type
        ,business_id
        ,business_name
)
insert overwrite table jms_dm.dm_spec_waybill_detail_dt partition(dt)
select 
    detail.waybill_no             as waybill_no             -- 运单
    , substr(detail.input_time, 1, 10)   as input_date      -- 录入日期
    , detail.input_time           as input_time             -- 录入时间
    , business_type_code          as business_type_code     -- 业务类型code
    , business_type_name          as business_type_name     -- 业务类型名称
    , business_type_name_en       as business_type_name_en  -- 业务类型英文名称
    , detail.input_network_id     as input_network_id       -- 录入网点id
    , detail.input_network_code   as input_network_code     -- 录入网点code
    , detail.input_network_name   as input_network_name     -- 录入网点名称
    , dim_network.agent_code      as agent_code             -- 代理区编码
    , dim_network.agent_name      as agent_name             -- 代理区名称
    , detail.business_id          as brand_id               -- 品牌id
    , detail.business_name        as brand_name             -- 品牌名称
    , nvl(remind_detail.remind_cnt, 0)    as remind_cnt             -- 提醒量
    , detail.dt
from 
    detail 
    left join 
        remind_detail   
            on detail.waybill_no = remind_detail.billcode 
           and detail.business_type_code = remind_detail.spec_type
           and detail.business_id = remind_detail.business_id
    left join 
        jms_dim.dim_network_whole_massage dim_network
              on detail.input_network_code = dim_network.code
 distribute by pmod(hash(rand()), 5)
;